﻿namespace Utils.DatabaseUtils
{
    using MySql.Data.MySqlClient;
    using System;
    using System.Data;

    public class MySQLExecuteData
    {
        private MySqlConnection Conn;
        private MySqlTransaction Tran;

        public MySQLExecuteData()
        {
            this.Conn = MySQLConnect.GetConnection();
        }

        public MySQLExecuteData(string str_key)
        {
            this.Conn = MySQLConnect.GetConnection(str_key);
        }

        public int ExecProcedure(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            int result = -1;
            MySqlCommand Cmd = new MySqlCommand();
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                this.Conn.Open();
                result = Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return result;
        }

        public int ExecProcedure(string strProcedure, string[] arrParaNames, object[] arrValues, MySqlDbType[] arrType)
        {
            int result = -1;
            MySqlCommand Cmd = new MySqlCommand();
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.Add("@p" + arrParaNames[i], arrType[i]).Value = arrValues[i];
                }
                this.Conn.Open();
                result = Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return result;
        }

        public int ExecProcedure(string strProcedure, string[] arrParaNames, string[] arrValues, ref string strResult)
        {
            int result = -1;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand {
                    CommandTimeout = 180,
                    Connection = this.Conn,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = strProcedure
                };
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                MySqlParameter p = Cmd.Parameters.Add("@p" + strResult, SqlDbType.Int);
                p.Direction = ParameterDirection.Output;
                this.Conn.Open();
                result = Cmd.ExecuteNonQuery();
                strResult = Convert.ToString(p.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return result;
        }

        public DataTable ExecProcedure_Return(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            DataTable CS10000;
            MySqlCommand Cmd = new MySqlCommand();
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                this.Conn.Open();
                MySqlDataReader mReader = Cmd.ExecuteReader();
                DataTable result = new DataTable();
                result.Load(mReader);
                CS10000 = result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public int ExecProcedureTran(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            int CS10000;
            MySqlCommand Cmd = new MySqlCommand();
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                this.Conn.Open();
                this.Tran = this.Conn.BeginTransaction();
                Cmd.Transaction = this.Tran;
                int result = Cmd.ExecuteNonQuery();
                this.Tran.Commit();
                CS10000 = result;
            }
            catch (Exception ex)
            {
                this.Tran.Rollback();
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public int ExecQuery(string str_Query)
        {
            int CS10000;
            MySqlCommand Cmd = new MySqlCommand();
            try
            {
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.Text;
                Cmd.CommandText = str_Query;
                this.Conn.Open();
                CS10000 = Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public int ExecQueryTran(string str_Query)
        {
            int CS10000;
            MySqlCommand Cmd = new MySqlCommand();
            try
            {
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.Text;
                Cmd.CommandText = str_Query;
                this.Conn.Open();
                this.Tran = this.Conn.BeginTransaction();
                Cmd.Transaction = this.Tran;
                int result = Cmd.ExecuteNonQuery();
                this.Tran.Commit();
                CS10000 = result;
            }
            catch (Exception ex)
            {
                this.Tran.Rollback();
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }
    }
}

